﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data;
using DTO;
using UTIL;

namespace DAO
{
    public class HoaDonDao
    {
        public static List<HoaDonDto> layDanhSachHoaDonThang(int thang, int nam)
        {
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachHoaDonTrongThang");
            StoreUtilities.AddInputPara(cmd, "@thang", SqlDbType.Int, thang);
            StoreUtilities.AddInputPara(cmd, "@nam", SqlDbType.Int, nam);
            SqlDataReader dr = cmd.ExecuteReader();

            List<HoaDonDto> danhsach = new List<HoaDonDto>();
            HoaDonDto hoadon = null;
            while (dr.Read())
            {
                hoadon = new HoaDonDto();
                hoadon.ID = (int)dr["ID"];
                hoadon.IDNguoiDung = (int)dr["IDNguoidung"];
                hoadon.Ngay = (DateTime)dr["ngay"];
                hoadon.TinhTrang = (int)dr["TinhTrang"];
                hoadon.GiaTri = (int)dr["giatri"];
                hoadon.Tendangnhap = (String)dr["tendangnhap"];

                danhsach.Add(hoadon);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }

        public static List<HoaDonDto> layDanhSachHoaDonQui(int qui, int nam)
        {
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachHoaDonTrongQui");
            StoreUtilities.AddInputPara(cmd, "@qui", SqlDbType.Int, qui);
            StoreUtilities.AddInputPara(cmd, "@nam", SqlDbType.Int, nam);
            SqlDataReader dr = cmd.ExecuteReader();

            List<HoaDonDto> danhsach = new List<HoaDonDto>();
            HoaDonDto hoadon = null;
            while (dr.Read())
            {
                hoadon = new HoaDonDto();
                hoadon.ID = (int)dr["ID"];
                hoadon.IDNguoiDung = (int)dr["IDNguoidung"];
                hoadon.Ngay = (DateTime)dr["ngay"];
                hoadon.TinhTrang = (int)dr["TinhTrang"];
                hoadon.GiaTri = (int)dr["giatri"];
                hoadon.Tendangnhap = (String)dr["tendangnhap"];

                danhsach.Add(hoadon);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }

        public static List<HoaDonDto> layDanhSachHoaDonNam(int nam)
        {
            SqlConnection cnn = DataConnector.MakeConnection();

            SqlCommand cmd = StoreUtilities.CreateStoreCommand(cnn, "sp_layDanhSachHoaDonTrongNam");
            StoreUtilities.AddInputPara(cmd, "@nam", SqlDbType.Int, nam);
            SqlDataReader dr = cmd.ExecuteReader();

            List<HoaDonDto> danhsach = new List<HoaDonDto>();
            HoaDonDto hoadon = null;
            while (dr.Read())
            {
                hoadon = new HoaDonDto();
                hoadon.ID = (int)dr["ID"];
                hoadon.IDNguoiDung = (int)dr["IDNguoidung"];
                hoadon.Ngay = (DateTime)dr["ngay"];
                hoadon.TinhTrang = (int)dr["TinhTrang"];
                hoadon.GiaTri = (int)dr["giatri"];
                hoadon.Tendangnhap = (String)dr["tendangnhap"];

                danhsach.Add(hoadon);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }

        public static DTO.HoaDonDto LayHoaDonTheoID(int id)
        {
            SqlConnection cnn = DataConnector.MakeConnection();

            string sql = "Select * From HoaDon where ID=" + id;
            SqlCommand cmd = new SqlCommand(sql, cnn);
            SqlDataReader dr = cmd.ExecuteReader();

            
            HoaDonDto hoadon = null;
            if(dr.Read())
            {
                hoadon = new HoaDonDto();
                hoadon.ID = (int)dr["ID"];
                hoadon.IDNguoiDung = (int)dr["IDNguoiDung"];
                hoadon.Ngay = (DateTime)dr["ngay"];
                hoadon.GiaTri = (int)dr["GiaTri"];
                hoadon.TinhTrang = (int)dr["TinhTrang"];
                hoadon.HoTen = (String)dr["HoTen"];
                hoadon.DiaChi = (String)dr["DiaChi"];
                hoadon.DienThoai = (String)dr["DienThoai"];
                hoadon.Email = (String)dr["Email"];
                hoadon.KieuThanhToan = (int)dr["KieuThanhToan"];

                
            }
            // dong ket noi
            cnn.Close();

            return hoadon;
        }

        public static bool XoaHoaDon(int id)
        {
            try
            {
                HoaDonDto hoadon = LayHoaDonTheoID(id);
                if (hoadon.TinhTrang != 1)
                {
                    return false;
                }
                SqlConnection cnn = DataConnector.MakeConnection();

                string sql = "Delete From HoaDon where ID=" + id;
                SqlCommand cmd = new SqlCommand(sql, cnn);
                int i = cmd.ExecuteNonQuery();
                if (i != 1)
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        public static int LayHoaDonIDLonNhat()
        {
            SqlConnection cnn = DataConnector.MakeConnection();
            string sql = "Select * From HoaDon order by ID DESC";
            SqlCommand cmd = new SqlCommand(sql, cnn);
            SqlDataReader dr = cmd.ExecuteReader();

            int nextID = 1;
            if (dr.HasRows == true)
            {
                dr.Read();
                nextID = (int)dr["ID"] + 1;
            }

            return nextID;
        }

        public static bool ThemHoaDon(GioHangDto GioHang)
        {
            try
            {
                HoaDonDto newHoaDon = new HoaDonDto();
                newHoaDon.ID = LayHoaDonIDLonNhat();
                newHoaDon.IDNguoiDung = GioHang.IDNguoiDung;
                newHoaDon.GiaTri = (int)GioHang.ThanhTien;
                newHoaDon.Ngay = GioHang.Ngay;
                newHoaDon.HoTen = GioHang.HoTen;
                newHoaDon.DiaChi = GioHang.DiaChi;
                newHoaDon.DienThoai = GioHang.DienThoai;
                newHoaDon.Email = GioHang.Email;
                newHoaDon.KieuThanhToan = GioHang.KieuThanhToan;
                // -1: chua xu ly; 0: dang giao, 1: da nhan
                newHoaDon.TinhTrang = -1;

                SqlConnection cnn = DataConnector.MakeConnection();

                string sql = "Insert into HoaDon(ID, IDNguoiDung, Ngay, GiaTri, TinhTrang, HoTen, DiaChi, DienThoai, Email, KieuThanhToan) values(" + newHoaDon.ID + ", " + newHoaDon.IDNguoiDung + ",'" + newHoaDon.Ngay + "', " + newHoaDon.GiaTri + ", " + newHoaDon.TinhTrang + ", N'" + newHoaDon.HoTen + "', N'" + newHoaDon.DiaChi + "', '" + newHoaDon.DienThoai + "', '" + newHoaDon.Email + "', " + newHoaDon.KieuThanhToan + ")";
                SqlCommand cmd = new SqlCommand(sql, cnn);
                int i = cmd.ExecuteNonQuery();
                if (i != 1)
                    return false;
                bool res = DAO.ChiTietHoaDonDao.ThemChiTietHoaDon(GioHang, newHoaDon.ID);
                // Neu that bai trong viec them vao chi tiet hoa don se xoa hoa don tuong ung
                if (res == false)
                {
                    XoaHoaDon(newHoaDon.ID);
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        public static List<DTO.HoaDonDto> LayHoaDonTheoIDNguoiDung(int idUser)
        {
            SqlConnection cnn = DataConnector.MakeConnection();

            string sql = "Select * From HoaDon Where IDNguoiDung=" + idUser + " order by ngay DESC";
            SqlCommand cmd = new SqlCommand(sql, cnn);
            SqlDataReader dr = cmd.ExecuteReader();

            List<HoaDonDto> danhsach = new List<HoaDonDto>();
            HoaDonDto hoadon = null;
            while (dr.Read())
            {
                hoadon = new HoaDonDto();
                hoadon.ID = (int)dr["ID"];
                hoadon.IDNguoiDung = (int)dr["IDNguoiDung"];
                hoadon.Ngay = (DateTime)dr["ngay"];
                hoadon.GiaTri = (int)dr["GiaTri"];
                hoadon.TinhTrang = (int)dr["TinhTrang"];
                hoadon.HoTen = (String)dr["HoTen"];
                hoadon.DiaChi = (String)dr["DiaChi"];
                hoadon.DienThoai = (String)dr["DienThoai"];
                hoadon.Email = (String)dr["Email"];
                hoadon.KieuThanhToan = (int)dr["KieuThanhToan"];

                danhsach.Add(hoadon);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }

        public static List<DTO.HoaDonDto> LayDanhSachHoaDon()
        {
            SqlConnection cnn = DataConnector.MakeConnection();

            string sql = "select * from HoaDon order by ngay DESC";
            SqlCommand cmd = new SqlCommand(sql, cnn);
            SqlDataReader dr = cmd.ExecuteReader();

            List<HoaDonDto> danhsach = new List<HoaDonDto>();
            HoaDonDto hoadon = null;
            while (dr.Read())
            {
                hoadon = new HoaDonDto();
                hoadon.ID = (int)dr["ID"];
                hoadon.IDNguoiDung = (int)dr["IDNguoiDung"];
                hoadon.Ngay = (DateTime)dr["ngay"];
                hoadon.GiaTri = (int)dr["GiaTri"];
                hoadon.TinhTrang = (int)dr["TinhTrang"];
                hoadon.HoTen = (String)dr["HoTen"];
                hoadon.DiaChi = (String)dr["DiaChi"];
                hoadon.DienThoai = (String)dr["DienThoai"];
                hoadon.Email = (String)dr["Email"];
                hoadon.KieuThanhToan = (int)dr["KieuThanhToan"];

                danhsach.Add(hoadon);
            }
            // dong ket noi
            cnn.Close();

            return danhsach;
        }

        public static bool CapNhatTinhTrangHoaDon(int idHoaDon, int TinhTrang)
        {
            SqlConnection cnn = DataConnector.MakeConnection();

            string sql = "update HoaDon set TinhTrang=" + TinhTrang + " where ID=" + idHoaDon;
            SqlCommand cmd = new SqlCommand(sql,cnn);
            int res = cmd.ExecuteNonQuery();
            if (res != 1)
                return false;
            return true;
        }

    }
}
